﻿using System;
using System.Collections.Generic;
using System.Text;
using RFIDStockManagement.Model.Public;
using RFIDStockManagement.Common.Public;
using System.Data;
using RFIDStockManagement.Model.InterFace;
using System.Data.Common;
using RFIDStockManagement.IDAL;

namespace RFIDStockManagement.DAL
{
    public class InStockBillAcess : IInventory,IInStockBill
    {
        private DbHelper _db;
        private const string INSERT_INSTOCKBILL = "Insert Into rfid_InStockbill(FID,FBillerID,FStockID,FBillDate,FSupplierID,FInStockPerson,FItemProperty)Values(";
        private const string INSERT_INSTOCKBILLENTRY = "Insert Into rfid_InStockbillEntry(FId,FEntryID,FIndex,FItemID,FStockPlaceID,FUnitID,FQty,FScanTime)Values(";
        private const string INSERT_INSTOCKBILLDETAIL = "Insert Into rfid_InStockbillDetail(FID,FEntryID,FItemID,FRFID)Values(";
        private List<InventoryData> _inventoryDatas;
        public InStockBillAcess()
        {
            _db = new DbHelper();
        }

        /// <summary>
        /// 用于产生入库单ID
        /// </summary>
        /// <returns></returns>
        public static long GetBillID()
        {
            return DALHelper.GetBillID("rfid_InStockbill");
        }

        /// <summary>
        /// 用于产生入库单明细的ID
        /// </summary>
        /// <returns></returns>
        public static long GetEntryID()
        {
            return DALHelper.GetBillID("rfid_InStockbillEntry");
        }

        public static long GetEntryID(int increment)
        {
            return DALHelper.GetBillID("rfid_InStockbillEntry", increment);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="billInfo"></param>
        /// <param name="transA"></param>
        /// <param name="errString"></param>
        /// <returns></returns>
        public bool InsertInStockBill(InStockBill billInfo, object transA, out string errString)
        {
            StringBuilder sql;
            StringBuilder sql2;
            long startID;

            Trans trans = transA as Trans;

            errString = string.Empty;

            try
            {

                #region 处理参数
                if (billInfo.FID <= 0)
                {
                    billInfo.FID = GetBillID();
                }
                if (billInfo.Entries.Count > 0)
                {
                    if (billInfo.Entries[0].Fid <= 0)//重新要取EntryID
                    {
                        startID = GetEntryID(billInfo.Entries.Count);
                        foreach (InStockBillEntry x in billInfo.Entries)
                        {
                            x.FEntryId = startID;
                            startID++;
                        }
                    }
                }

                #endregion

                sql = new StringBuilder();
                sql.AppendLine(INSERT_INSTOCKBILL);
                sql.Append(PublicFunction.AddToValueWithCom(billInfo.FID));
                sql.Append(PublicFunction.AddToValueWithCom(billInfo.BillerID));
                sql.Append(PublicFunction.AddToValueWithCom(billInfo.FStockID));
                sql.Append("GetDate(),");
                sql.Append(PublicFunction.AddToValueWithCom(billInfo.FSupplierID));
                sql.Append(PublicFunction.AddToStringWithCom(billInfo.FInStockPerson));
                sql.Append(((int)billInfo.FItemProperty).ToString());
                sql.AppendLine(")");

                DbCommand cmd = _db.GetSqlStringCommand(sql.ToString());
                _db.ExecuteNonQuery(cmd, trans);


                sql.Remove(0, sql.Length);
                sql2 = new StringBuilder();

                //这里我们要处理一次重复信息
                //所以先插入明细的,然后再根据明细的去更新,entry里面的数据
                foreach (InStockBillEntry x in billInfo.Entries)
                {

                    sql.Append(INSERT_INSTOCKBILLENTRY);
                    sql.Append(PublicFunction.AddToValueWithCom(billInfo.FID));
                    sql.Append(PublicFunction.AddToValueWithCom(x.FEntryId));
                    sql.Append(PublicFunction.AddToValueWithCom(x.FIndex));
                    sql.Append(PublicFunction.AddToValueWithCom(x.FItemID));
                    sql.Append(PublicFunction.AddToValueWithCom(x.FStockPlaceID));
                    sql.Append(PublicFunction.AddToValueWithCom(x.FUnitID));
                    sql.Append(PublicFunction.AddToValueWithCom(x.FQty));
                    sql.Append(PublicFunction.AddToString(x.FScanTime.ToString(PublicConst.LongTimeFormater)));
                    //sql.Append("GetDate()");
                    sql.AppendLine(")");

                    foreach (InStockDetail xx in x.RFIDDetails)
                    {
                        sql2.Append(INSERT_INSTOCKBILLDETAIL);
                        sql2.Append(PublicFunction.AddToValueWithCom(billInfo.FID));
                        sql2.Append(PublicFunction.AddToValueWithCom(x.FEntryId));
                        sql2.Append(PublicFunction.AddToValueWithCom(xx.FItemID));
                        sql2.Append(PublicFunction.AddToString(xx.FRFID));
                        sql2.AppendLine(")");
                    }
                }

                if (sql.Length > 0)
                {
                    cmd = _db.GetSqlStringCommand(sql.ToString());
                    _db.ExecuteNonQuery(cmd, trans);

                    sql.Remove(0, sql.Length);

                }

                if (sql2.Length > 0) //把明细插入进去
                {
                    cmd = _db.GetSqlStringCommand(sql2.ToString());
                    _db.ExecuteNonQuery(cmd, trans);

                    sql2.Remove(0, sql2.Length);

                    //把明细插入进去以后,把已经入库的部分部分要去除掉
                    sql2.AppendLine("Delete t1  ");
                    sql2.AppendLine("From rfid_InStockbillDetail t1");
                    sql2.AppendLine("Left Join rfid_Item_Detail t2 on  t1.FitemID = t2.FItemID And t1.FRFID = t2.FRFID");
                    sql2.Append("Where t1.FId = ");
                    sql2.Append(billInfo.FID.ToString());
                    sql2.AppendLine(" And isnull(t2.FStatus,0) = 1");

                    //修正明细里面重复的数据
                    GetRemoveUnnecessaryDataSql(billInfo.FID, sql2);

                    //插入item_detail表中不存在的数据
                    sql2.AppendLine("Insert Into rfid_Item_Detail(FItemID,FRFID,FLastInStockID,FStatus)");
                    sql2.AppendLine("Select t1.FItemID,t1.FRFID,"+billInfo.FStockID.ToString()+",1");
                    sql2.AppendLine("From rfid_InStockbillDetail t1 Left Join rfid_Item_Detail t2 on  t1.FitemID = t2.FItemID And t1.FRFID = t2.FRFID");
                    sql2.AppendLine("Where t2.Fid is null And t1.Fid = "+ billInfo.FID.ToString());

                    //这里更新物料明细表中的状态和最后入库数据
                    sql2.AppendLine("Update t1 Set FStatus = 1,FLastInStockID =" + billInfo.FStockID.ToString() + ",FLastInStockPlaceId = t3.FStockPlaceID,FLastInTime = getDate(),FUnitId = t3.FUnitID,FRemaindQty = t4.FRate");
                    sql2.AppendLine("From rfid_Item_Detail t1 ");
                    sql2.AppendLine("Inner join rfid_InStockbillDetail t2 on  t1.FitemID = t2.FItemID And t1.FRFID = t2.FRFID");
                    sql2.AppendLine("Inner Join rfid_InStockbillEntry t3 on t2.FEntryId = t3.FEntryID And t3.Fid = " +billInfo.FID.ToString());
                    sql2.AppendLine("Inner Join rfid_Measure t4 on t3.FUnitID = t4.FId ");
                    sql2.AppendLine("Where t2.Fid = " + billInfo.FID.ToString());
                    cmd = _db.GetSqlStringCommand(sql2.ToString()) ;

                    _db.ExecuteNonQuery(cmd, trans);

                    sql2.Remove(0, sql2.Length);

                }


                sql.AppendLine("Update t1 Set FQty =t2.FQty  From rfid_InStockbillEntry t1");
                sql.AppendLine("Inner Join (select FEntryID,Count(FDetailID) as FQty From rfid_InStockbillDetail ");
                sql.Append("Where Fid = " + billInfo.FID.ToString());
                sql.AppendLine(" Group by FEntryID) t2 on t1.FEntryID = t2.FEntryID");
                sql.AppendLine("Where t1.Fid = " +billInfo.FID.ToString());

                cmd = _db.GetSqlStringCommand(sql.ToString());//这里来更新入库单的entry数量

                _db.ExecuteNonQuery(cmd, trans);

                #region 为及时库存提供数据源

                GetInventoryList(billInfo.FID, billInfo.FStockID, cmd, trans);

                #endregion


            }
            catch (Exception ex)
            {
                errString = ex.Message;
                return false;
            }

            return true;
        }

        /// <summary>
        /// 删除重复的数据
        /// </summary>
        /// <param name="billId"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        private void GetRemoveUnnecessaryDataSql(long billId,StringBuilder sql)
        {
            sql.AppendLine("Create Table #InstockDetailTmpFids(fid bigint not null default(0))");
            sql.AppendLine("Insert Into #InstockDetailTmpFids(fid) Select Max(FDetailID) From rfid_InStockbillDetail Where Fid =" + billId.ToString() +"   group by FRFID");
            sql.AppendLine("Delete From t1 From rfid_InStockbillDetail t1 Left join #InstockDetailTmpFids t2 on t1.FDetailID = t2.FId Where t2.Fid is null And t1.Fid =" + billId.ToString());
            sql.AppendLine("Drop table  #InstockDetailTmpFids");
        }

        /// <summary>
        /// 取即使库存要用到的信息
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="tran"></param>
        private void GetInventoryList(long fid, long stotckID,DbCommand cmd, Trans tran)
        {
            InventoryData newData;
            cmd.CommandText = "Select FItemID,FUnitID,FQty From rfid_InStockbillEntry Where Fid =" + fid.ToString();

            DataTable dt = _db.ExecuteDataTable(cmd, tran);
            _inventoryDatas = new List<InventoryData>();

            foreach (DataRow xxx in dt.Rows)
            {
                newData = new InventoryData();
                newData.FItemID = PublicFunction.NotNullLng(xxx["FItemID"]);
                newData.FQty = PublicFunction.NotNullDbl(xxx["FQty"]);
                newData.FUnitID = PublicFunction.NotNullLng(xxx["FUnitID"]);
                newData.FStockId = stotckID;
                _inventoryDatas.Add(newData);
                
            }
        }

        #region IInventory 成员

        public List<InventoryData> GetInventoryDatas()
        {
            return _inventoryDatas;
        }

        #endregion
    }
}
